package com.zenithsun.news.statistics.dao.impl;

import java.util.HashMap;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.springframework.stereotype.Repository;
import com.syzg.common.dao.impl.BaseDAOHibernate;
import com.zenithsun.news.statistics.dao.StatisticsDAO;
import com.zenithsun.news.statistics.model.ColumnNewsPV;
import com.zenithsun.news.statistics.model.NewsPV;

@Repository("statisticsDAO")
public class StatisticsDAOImpl extends BaseDAOHibernate<NewsPV> implements StatisticsDAO{
	
	@Override
	public List<HashMap<String, Object>> listColumnsNewsCounter() throws Exception{
		String sql="SELECT a.name,a.wordTotal,b.picTotal FROM(SELECT x.name,ifnull(y.wordTotal,0) AS wordTotal FROM newscolumns x LEFT JOIN(SELECT t.news_columns_id,COUNT(*)wordTotal FROM newsinfo t LEFT JOIN review d ON t.id = d.busId WHERE t.isDelete=0 AND d.state=3 AND t.type ='word' GROUP BY t.news_columns_id) y ON x.id=y.news_columns_id) a RIGHT JOIN(SELECT x.name, ifnull(y.picTotal,0) AS picTotal FROM newscolumns x LEFT JOIN(SELECT t.news_columns_id, COUNT(*) picTotal FROM newsinfo t LEFT JOIN review d ON t.id = d.busId WHERE t.isDelete=0	AND d.state=3 AND t.type='pic' GROUP BY t.news_columns_id) y ON x.id=y.news_columns_id) b ON a.name = b.name";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	
	@Override
	public List<HashMap<String, Object>> listColumnsNewsPVCounter() throws Exception{
		String sql="SELECT e.name, e.picCounter, f.wordCounter FROM(SELECT c.name,ifnull(a.picCounter,0)as picCounter FROM newscolumns c LEFT JOIN (SELECT a.news_columns_id,SUM(page_view_counter)as picCounter FROM (select n.news_columns_id,n.type,n.page_view_counter from newsinfo n LEFT JOIN review r on n.id=r.busId where r.state=3 and n.isDelete=0 and n.type='pic')a GROUP BY a.news_columns_id)a on c.id=a.news_columns_id)e LEFT JOIN (SELECT c.name,ifnull(a.wordCounter,0)as wordCounter FROM newscolumns c LEFT JOIN (SELECT a.news_columns_id,SUM(page_view_counter)as wordCounter FROM (select n.news_columns_id,n.type,n.page_view_counter from newsinfo n LEFT JOIN review r on n.id=r.busId where r.state=3 and n.isDelete=0 and n.type='word')a GROUP BY a.news_columns_id)a on c.id=a.news_columns_id)f on e.name=f.name";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	
	@Override
	public List<HashMap<String, Object>> listNewsDailyPVCounter() throws Exception{
		String sql="SELECT pic_pv_ picCounter,word_pv_ wordCounter,concat_ws('-',year_,month_,day_) date FROM news_pv_ order by concat_ws('-',year_,month_,day_) desc limit 10";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}

	@Override
	public List<HashMap<String, Object>> listNewsMonthPVCounter() throws Exception{
		//String sql="SELECT CONCAT(year,-month)as date,SUM(picCounter)as picCounter,SUM(wordCounter)as wordCounter FROM statistics";
		String sql="SELECT concat_ws('-',year_,month_) date,SUM(pic_pv_) picCounter,SUM(word_pv_) wordCounter FROM news_pv_ group by concat_ws('-',year_,month_)";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	@Override
	protected Class<NewsPV> getEntityClass() {
		// TODO Auto-generated method stub
		return NewsPV.class;
	}
	
	@Override
	public List<HashMap<String, Object>> findPVCounter() throws Exception {
		// TODO Auto-generated method stub
		String sql ="select type,sum(t.page_view_counter) as total from newsinfo t where type is not null group by type";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	
	@Override
	public List<HashMap<String, Object>> findNewsPV(NewsPV pv) throws Exception {
		String sql ="select * from news_pv_ where year_='"+pv.getYear()+"' and month_='"+pv.getMonth()+"' and day_='"+pv.getDay()+"'";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	
	@Override
	public List<HashMap<String, Object>> findNewsPVTotal(NewsPV pv) throws Exception {
		// TODO Auto-generated method stub
		String sql ="select sum(pv_) pv,sum(pic_pv_) picPv,sum(word_pv_) wordPv from news_pv_";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	
	@Override
	public boolean saveNewsPV(NewsPV pv) throws Exception {
		// TODO Auto-generated method stub
		String sql ="insert into news_pv_(id_,year_,month_,day_,time_,pv_,pic_pv_,word_pv_) "
				+ "values('"+pv.getId()+"','"+pv.getYear()+"','"+pv.getMonth()+"','"+pv.getDay()+"','"+pv.getTime()+"','"+pv.getPv()+"','"+pv.getPicPv()+"','"+pv.getWordPv()+"')";
		SQLQuery query = getSession().createSQLQuery(sql);
		return query.executeUpdate() > 0;
	}
	
	@Override
	public List<HashMap<String, Object>> findColumnPVCounter() throws Exception {
		// TODO Auto-generated method stub
		String sql ="select a.id, a.`name`,b.total from newscolumns a left join (select news_columns_id column_id,sum(page_view_counter) total from newsinfo group by news_columns_id) b on a.id=b.column_id";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	@Override
	public List<HashMap<String, Object>> findNewsPVTotalByColumnId(ColumnNewsPV pv) throws Exception {
		// TODO Auto-generated method stub
		String sql ="select * from column_news_pv_ where year_='"+pv.getYear()+"' and month_='"+pv.getMonth()+"' and day_='"+pv.getDay()+"' and column_id_='"+pv.getColumnId()+"'";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	@Override
	public boolean saveColumnNewsPV(ColumnNewsPV pv) throws Exception {
		// TODO Auto-generated method stub
				String sql ="insert into column_news_pv_(id_,year_,month_,day_,time_,column_id_,pv_) "
						+ "values('"+pv.getId()+"','"+pv.getYear()+"','"+pv.getMonth()+"','"+pv.getDay()+"','"+pv.getTime()+"','"+pv.getColumnId()+"','"+pv.getPv()+"')";
				SQLQuery query = getSession().createSQLQuery(sql);
				return query.executeUpdate() > 0;
	}
	@Override
	public List<HashMap<String, Object>> findPVTotalGroupColumnId() throws Exception {
		// TODO Auto-generated method stub
		String sql ="select column_id_ column_id,sum(pv_) total from column_news_pv_ group by column_id_";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
}
